﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;

namespace QuanLySieuThi_08520114_08520220
{
    class controller_nhanvien
    {
        private SqlCeDataAdapter temp_adapter;
        private DataTable temp_table;
        private SqlCeConnection con = null;

        /*===================  Chức vụ,Bộ Phận,Công Việc ====================*/
        public DataTable list_data(string ten_table)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From " + ten_table;
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool del_data(string ten_table,string ma,string hauto)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Delete From " + ten_table + " Where Ma" + hauto + " = '" + ma + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool add_data(string ten_table,string ten,string hauto)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From " + ten_table + " Where Ten"+ hauto +" ='" + ten + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Tên " + hauto + " đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Insert Into "+ ten_table +"(Ten"+ hauto +") VALUES ('" + ten + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool update_data(string ma, string ten,string ten_table,string hauto)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From " + ten_table + " Where Ten" + hauto + " ='" + ten + "' and Ma"+hauto+" != '" + ma + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Tên " + hauto + " đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Update "+ten_table+" Set Ten"+hauto+" = '" + ten + "' Where Ma"+hauto+" = '" + ma + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        /*============================================================*/

        /*===================== Nhân Viên ============================*/
        public DataTable list_nhanvien()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From NHANVIEN n LEFT JOIN CHUCVU ch on n.MaChucVu = ch.MaChucVu Left join BOPHAN b on n.MaBoPhan = b.MaBoPhan Left join CONGVIEC c on n.MaCongViec = c.MaCongViec";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public DataTable get_nhanvien(string ma_nv)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From NHANVIEN n LEFT JOIN CHUCVU ch on n.MaChucVu = ch.MaChucVu Left join BOPHAN b on n.MaBoPhan = b.MaBoPhan Left join CONGVIEC c on n.MaCongViec = c.MaCongViec where NhanVienID ='"+ma_nv+"'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public string get_id(string ten_table,string hauto ,string ten)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";


                    sql = "Select Ma"+hauto+" From "+ten_table+" where Ten"+hauto+" = '" + ten + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    string ma = Convert.ToString(cmd.ExecuteScalar());
                    ToanCuc.disconnect(ref con);
                    return ma;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool add_nhanvien(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From NHANVIEN Where MaNhanVien ='" + data[0] + "' or SoHopDong ='" + data[12] + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Mã Nhân Viên hoặc Số Hợp Đồng đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Insert Into NHANVIEN (MaNhanVien,MaChucVu,MaBoPhan,MaCongViec,HoTen,NgaySinh,QueQuan,DiaChiThuongTru,DiaChiHienNay,DienThoai,NgayVaoLam,TrinhDo,SoHopDong,ThoiHanHopDong,MucLuongHienTai,MucLuongKhoiDiem,NgayKyHopDong,SoNgayDiLam,NghiKhongPhep,SoNgayDiTre,SoLanBiKhienTrach) VALUES ('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "','" + data[8] + "','" + data[9] + "','" + data[10] + "','" + data[11] + "','" + data[12] + "','" + data[13] + "','" + data[14] + "','" + data[15] + "','" + data[16] + "','" + data[17] + "','" + data[18] + "','" + data[19] + "','" + data[20] + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool del_nhanvien(string ma_nv)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Delete From BANGLUONG Where NhanVienID = '" + ma_nv + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "Delete From NHANVIEN Where NhanVienID = '" + ma_nv + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool update_nhanvien(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From NHANVIEN Where ( MaNhanVien ='" + data[0] + "' or SoHopDong ='" + data[12] + "') And NhanVienID != '"+data[21]+"'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Mã Nhân Viên hoặc Số Hợp Đồng đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Update NHANVIEN Set MaNhanVien = '" + data[0] + "',MaChucVu = '" + data[1] + "',MaBoPhan = '" + data[2] + "',MaCongViec = '" + data[3] + "',HoTen = '" + data[4] + "',NgaySinh = '" + data[5] + "',QueQuan = '" + data[6] + "',DiaChiThuongTru = '" + data[7] + "',DiaChiHienNay = '" + data[8] + "',DienThoai = '" + data[9] + "',NgayVaoLam = '" + data[10] + "',TrinhDo = '" + data[11] + "',SoHopDong = '" + data[12] + "',ThoiHanHopDong = '" + data[13] + "',MucLuongHienTai = '" + data[14] + "',MucLuongKhoiDiem = '" + data[15] + "',NgayKyHopDong = '" + data[16] + "',SoNgayDiLam = '" + data[17] + "',NghiKhongPhep = '" + data[18] + "',SoNgayDiTre = '" + data[19] + "',SoLanBiKhienTrach = '" + data[20] + "' Where NhanVienID = '" + data[21] + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }


        /*============================================================*/


    }
}
